from collections import defaultdict

from flask import Flask, request, jsonify, g
import sqlite3
import time
from flask_cors import CORS

app = Flask(__name__)
# 为整个应用开启跨域支持，指定允许的源
CORS(app, origins=["*"])
DATABASE ='survey.db'


# 自定义连接工厂函数，使查询结果以字典形式返回
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = dict_factory  # 设置连接的 row_factory 为自定义函数
    return db


@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()


def init_db():
    with app.app_context():
        db = get_db()
        cursor = db.cursor()
        # 创建问卷表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS surveys (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                content TEXT,
                created_at INTEGER
            )
        ''')
        # 创建问题表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS questions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                survey_id INTEGER,
                content TEXT NOT NULL,
                question_type TEXT CHECK(question_type IN ('a', 'b')),
                options TEXT,
                FOREIGN KEY(survey_id) REFERENCES surveys(id)
            )
        ''')
        # 创建答案表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS answers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                survey_id INTEGER,
                question_id INTEGER,
                answer_content TEXT,
                submitted_at INTEGER,
                FOREIGN KEY(survey_id) REFERENCES surveys(id),
                FOREIGN KEY(question_id) REFERENCES questions(id)
            )
        ''')
        db.commit()


# 初始化数据库
init_db()


@app.route('/', methods=['GET'])
def hello():
    return jsonify({'code': 200,'msg':'success'}), 200


@app.route('/api/surveys', methods=['POST'])
def create_survey():
    data = request.json
    db = get_db()
    cursor = db.cursor()

    try:
        print('Creating survey', data['title'], data['content'])
        print(data['typeList'])
        print(data['questionList'])
        print(data['optionList'])
        # 插入问卷基本信息
        cursor.execute(
            'INSERT INTO surveys (title, content, created_at) VALUES (?,?,?)',
            (data['title'], data['content'], int(time.time())))
        survey_id = cursor.lastrowid
        n = len(data['typeList'])
        for i in range(n):
            cursor.execute('''
                                INSERT INTO questions (survey_id, content, question_type, options)
                                VALUES (?,?,?,?)
                            ''', (survey_id,
                                  data['questionList'][i],
                                  data['typeList'][i],
                                  data['optionList'][i]
                                  ))

        db.commit()
        return jsonify({'code': 200,'msg': '发布成功'}), 200
    except Exception as e:
        print(e)
        db.rollback()
        return jsonify({'code': 400, 'error': str(e)}), 400

@app.route('/api/surveyList', methods=['GET'])
def get_surveyList():
    db = get_db()
    cursor = db.cursor()
    try:
        cursor.execute("SELECT * FROM surveys")
        data = cursor.fetchall()
        for i in data:
            survey_id = i['id']
            cursor.execute("SELECT * FROM questions WHERE survey_id=?", (survey_id,))
            questions = cursor.fetchall()
            i['typeList'] = []
            i['questionList'] = []
            i['optionList'] = []
            i['answer'] = []
            for j in questions:
                i['typeList'].append(j['question_type'])
                i['questionList'].append(j['content'])
                i['optionList'].append(j['options'])
                i['answer'].append("")
        return jsonify({'code': 200,'msg':'获取成功', 'data': data}), 200
    except Exception as e:
        print(e)
        db.rollback()
        return jsonify({'code': 400, 'error': str(e)}), 400


@app.route('/api/surveys/<int:survey_id>/submit', methods=['POST'])
def submit_answers(survey_id):
    data = request.json
    db = get_db()
    cursor = db.cursor()
    try:
        print(survey_id)
        print(data)
        cursor.execute("SELECT * FROM questions WHERE survey_id=?", (survey_id,))
        questions = cursor.fetchall()
        timestamp = int(time.time())
        for i in range(len(questions)):
            cursor.execute('''
                INSERT INTO answers (survey_id, question_id, answer_content, submitted_at)
                VALUES (?,?,?,?)
            ''', (survey_id, questions[i]['id'], data[i], timestamp))
        db.commit()
        return jsonify({'code': 200,'msg':'提交成功'}), 200
    except Exception as e:
        # db.rollback()
        return jsonify({'code': 400, 'error': str(e)}), 400


@app.route('/api/surveys/<int:survey_id>/analysis', methods=['GET'])
def get_stats(survey_id):
    try:
        db = get_db()

        # 验证问卷是否存在
        survey = db.execute('SELECT id FROM surveys WHERE id = ?', (survey_id,)).fetchone()
        if not survey:
            return jsonify({'code': 404, 'msg': '问卷不存在'}), 404

        # 获取所有问题
        questions = db.execute(
            'SELECT id, content, question_type, options FROM questions WHERE survey_id = ? ORDER BY id',
            (survey_id,)
        ).fetchall()

        # 获取所有答案并按问题分组
        all_answers = db.execute(
            'SELECT question_id, answer_content FROM answers WHERE survey_id = ?',
            (survey_id,)
        ).fetchall()

        answers_dict = defaultdict(list)
        for ans in all_answers:
            if ans['answer_content']:
                answers_dict[ans['question_id']].append(ans['answer_content'])

        analysis = []

        for q in questions:
            question_id = q['id']
            question_type = q['question_type']
            options = q['options'].split(',') if q['options'] else []
            answers = answers_dict.get(question_id, [])

            stats = {}
            if question_type == 'a':
                # 选择题统计（基于选项内容）
                valid_answers = []
                option_counts = {opt: 0 for opt in options}

                for ans in answers:
                    if ans in option_counts:
                        option_counts[ans] += 1
                        valid_answers.append(ans)

                total_valid = len(valid_answers)

                stats['type'] = '选择题'
                stats['total'] = total_valid
                stats['invalid_count'] = len(answers) - total_valid
                stats['options'] = [{
                    'option': opt,
                    'count': count,
                    'percent': round(count / total_valid * 100, 2) if total_valid > 0 else 0
                } for opt, count in option_counts.items()]

            elif question_type == 'b':
                # 主观题处理保持不变
                stats['type'] = '主观题'
                stats['total'] = len(answers)
                stats['samples'] = answers[:10]
                stats['wordcloud'] = len(answers) > 0

            analysis.append({
                'question_id': question_id,
                'content': q['content'],
                'stats': stats
            })

        return jsonify({
            'code': 200,
            'msg': '获取成功',
            'data': {
                'survey_id': survey_id,
                'analysis': analysis
            }
        })

    except Exception as e:
        print(f'Analysis error: {str(e)}')
        return jsonify({'code': 500, 'msg': '服务器内部错误'}), 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)